package com.supermarket.dao;

import com.supermarket.model.Product;
import com.supermarket.util.DatabaseUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品数据访问对象
 * 负责商品相关的数据库操作
 */
public class ProductDAO {

    /**
     * 获取所有商品（含库存信息）
     * 
     * @return 商品列表
     */
    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_sale_date, " +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.status = 'ACTIVE' " +
                "ORDER BY p.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setProductCode(rs.getString("product_code"));
                product.setProductName(rs.getString("product_name"));
                product.setCategoryId(rs.getInt("category_id"));
                product.setCategoryName(rs.getString("category_name"));
                product.setSupplierId(rs.getInt("supplier_id"));
                product.setSupplierName(rs.getString("supplier_name"));
                product.setUnit(rs.getString("unit"));
                product.setPurchasePrice(rs.getBigDecimal("purchase_price"));
                product.setSalePrice(rs.getBigDecimal("sale_price"));
                product.setShelfLifeDays(rs.getObject("shelf_life_days", Integer.class));
                product.setDescription(rs.getString("description"));
                product.setStatus(rs.getString("status"));
                product.setCreatedAt(rs.getTimestamp("created_at"));
                product.setUpdatedAt(rs.getTimestamp("updated_at"));
                product.setCurrentStock(rs.getBigDecimal("current_stock"));
                product.setMinStock(rs.getBigDecimal("min_stock"));
                product.setMaxStock(rs.getBigDecimal("max_stock"));
                product.setLastSaleDate(rs.getDate("last_sale_date"));
                product.setStockStatus(rs.getString("stock_status"));
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 根据商品编码或名称搜索商品
     * 
     * @param keyword 搜索关键词
     * @return 商品列表
     */
    public List<Product> searchProducts(String keyword) {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_sale_date, " +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.status = 'ACTIVE' " +
                "AND (p.product_code LIKE ? OR p.product_name LIKE ?) " +
                "ORDER BY p.product_name";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            String searchPattern = "%" + keyword + "%";
            ps.setString(1, searchPattern);
            ps.setString(2, searchPattern);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = createProductFromResultSet(rs);
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 根据商品编码获取商品详情
     * 
     * @param productCode 商品编码
     * @return 商品对象
     */
    public Product getProductByCode(String productCode) {
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_sale_date, " +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.product_code = ? AND p.status = 'ACTIVE'";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, productCode);
            rs = ps.executeQuery();

            if (rs.next()) {
                return createProductFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 根据商品ID获取商品详情
     * 
     * @param productId 商品ID
     * @return 商品对象
     */
    public Product getProductById(int productId) {
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_sale_date, " +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, productId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return createProductFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 添加新商品
     * 
     * @param product 商品对象
     * @return 是否添加成功
     */
    public boolean addProduct(Product product) {
        // 先检查商品编码是否已存在
        if (isProductCodeExists(product.getProductCode())) {
            throw new RuntimeException("商品编码 " + product.getProductCode() + " 已存在，请使用其他编码");
        }

        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;

        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 插入商品信息
            String sql1 = "INSERT INTO products (product_code, product_name, category_id, supplier_id, " +
                    "unit, purchase_price, sale_price, shelf_life_days, description) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            ps1 = conn.prepareStatement(sql1, PreparedStatement.RETURN_GENERATED_KEYS);
            ps1.setString(1, product.getProductCode());
            ps1.setString(2, product.getProductName());
            ps1.setInt(3, product.getCategoryId());
            ps1.setInt(4, product.getSupplierId());
            ps1.setString(5, product.getUnit());
            ps1.setBigDecimal(6, product.getPurchasePrice());
            ps1.setBigDecimal(7, product.getSalePrice());
            ps1.setObject(8, product.getShelfLifeDays());
            ps1.setString(9, product.getDescription());

            int rows1 = ps1.executeUpdate();

            if (rows1 > 0) {
                // 获取生成的商品ID
                ResultSet rs = ps1.getGeneratedKeys();
                if (rs.next()) {
                    int productId = rs.getInt(1);

                    // 插入库存信息
                    String sql2 = "INSERT INTO inventory (product_id, current_stock, min_stock, max_stock) " +
                            "VALUES (?, ?, ?, ?)";
                    ps2 = conn.prepareStatement(sql2);
                    ps2.setInt(1, productId);
                    ps2.setBigDecimal(2,
                            product.getCurrentStock() != null ? product.getCurrentStock() : BigDecimal.ZERO);
                    ps2.setBigDecimal(3, product.getMinStock() != null ? product.getMinStock() : BigDecimal.ZERO);
                    ps2.setBigDecimal(4,
                            product.getMaxStock() != null ? product.getMaxStock() : new BigDecimal("9999"));

                    int rows2 = ps2.executeUpdate();

                    if (rows2 > 0) {
                        conn.commit();
                        return true;
                    }
                }
            }

            conn.rollback();
            return false;

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DatabaseUtil.close(conn, ps1);
            DatabaseUtil.close(null, ps2);
        }
    }

    /**
     * 更新商品信息
     * 
     * @param product 商品对象
     * @return 是否更新成功
     */
    public boolean updateProduct(Product product) {
        String sql = "UPDATE products " +
                "SET product_name = ?, category_id = ?, supplier_id = ?, unit = ?, " +
                "    purchase_price = ?, sale_price = ?, shelf_life_days = ?, " +
                "    description = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, product.getProductName());
            ps.setInt(2, product.getCategoryId());
            ps.setInt(3, product.getSupplierId());
            ps.setString(4, product.getUnit());
            ps.setBigDecimal(5, product.getPurchasePrice());
            ps.setBigDecimal(6, product.getSalePrice());
            ps.setObject(7, product.getShelfLifeDays());
            ps.setString(8, product.getDescription());
            ps.setInt(9, product.getId());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 更新商品库存
     * 
     * @param productId    商品ID
     * @param currentStock 当前库存
     * @param minStock     最小库存
     * @param maxStock     最大库存
     * @return 是否更新成功
     */
    public boolean updateInventory(int productId, BigDecimal currentStock,
            BigDecimal minStock, BigDecimal maxStock) {
        String sql = "UPDATE inventory " +
                "SET current_stock = ?, min_stock = ?, max_stock = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE product_id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1, currentStock);
            ps.setBigDecimal(2, minStock);
            ps.setBigDecimal(3, maxStock);
            ps.setInt(4, productId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 删除商品（软删除）
     * 
     * @param productId 商品ID
     * @return 是否删除成功
     */
    public boolean deleteProduct(int productId) {
        String sql = "UPDATE products SET status = 'INACTIVE', updated_at = CURRENT_TIMESTAMP WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, productId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 获取低库存商品
     * 
     * @return 低库存商品列表
     */
    public List<Product> getLowStockProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_sale_date, " +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.status = 'ACTIVE' " +
                "AND i.current_stock <= i.min_stock " +
                "ORDER BY i.current_stock ASC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = createProductFromResultSet(rs);
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 获取临期商品列表（保质期少于7天的商品）
     * 
     * @return 临期商品列表
     */
    public List<Product> getExpiringProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_purchase_date, " +
                "       DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) AS remaining_days, "
                +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 3 THEN '即将过期' "
                +
                "           WHEN DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 7 THEN '临期商品' "
                +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.status = 'ACTIVE' " +
                "AND p.shelf_life_days IS NOT NULL " +
                "AND i.last_purchase_date IS NOT NULL " +
                "AND i.current_stock > 0 " +
                "AND DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 7 " +
                "ORDER BY remaining_days ASC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = createProductFromResultSet(rs);
                // 设置剩余天数
                product.setRemainingDays(rs.getInt("remaining_days"));
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 获取所有临期商品列表（30天内过期）
     * 
     * @return 临期商品列表
     */
    public List<Product> getAllExpiringProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, p.category_id, c.category_name, " +
                "       p.supplier_id, s.supplier_name, p.unit, p.purchase_price, p.sale_price, " +
                "       p.shelf_life_days, p.description, p.status, p.created_at, p.updated_at, " +
                "       i.current_stock, i.min_stock, i.max_stock, i.last_purchase_date, " +
                "       DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) AS remaining_days, "
                +
                "       CASE " +
                "           WHEN i.current_stock = 0 THEN '缺货' " +
                "           WHEN DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 0 THEN '已过期' "
                +
                "           WHEN DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 3 THEN '即将过期' "
                +
                "           WHEN DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 7 THEN '临期商品' "
                +
                "           WHEN DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 30 THEN '即将临期' "
                +
                "           WHEN i.current_stock <= i.min_stock THEN '低库存' " +
                "           ELSE '正常' " +
                "       END AS stock_status " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.status = 'ACTIVE' " +
                "AND p.shelf_life_days IS NOT NULL " +
                "AND i.last_purchase_date IS NOT NULL " +
                "AND i.current_stock > 0 " +
                "AND DATEDIFF(DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), CURDATE()) <= 30 " +
                "ORDER BY remaining_days ASC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = createProductFromResultSet(rs);
                // 设置剩余天数
                product.setRemainingDays(rs.getInt("remaining_days"));
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 申报临期商品
     * 
     * @param productId       商品ID
     * @param reportedBy      申报人ID
     * @param suggestedAction 建议处理方式
     * @param discountRate    建议折扣率
     * @param remarks         备注
     * @return 是否申报成功
     */
    public boolean declareExpiringProduct(int productId, int reportedBy, String suggestedAction,
            BigDecimal discountRate, String remarks) {
        String sql = "INSERT INTO expiring_products (product_id, reported_by, current_stock, expiry_date, " +
                "                                   suggested_action, discount_rate, status, report_date, remarks) " +
                "SELECT p.id, ?, i.current_stock, " +
                "       DATE_ADD(i.last_purchase_date, INTERVAL p.shelf_life_days DAY), " +
                "       ?, ?, 'PENDING', CURDATE(), ? " +
                "FROM products p " +
                "JOIN inventory i ON p.id = i.product_id " +
                "WHERE p.id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, reportedBy);
            ps.setString(2, suggestedAction);
            if (discountRate != null) {
                ps.setBigDecimal(3, discountRate);
            } else {
                ps.setBigDecimal(3, null);
            }
            ps.setString(4, remarks);
            ps.setInt(5, productId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 检查商品编码是否已存在
     * 
     * @param productCode 商品编码
     * @return 是否存在
     */
    public boolean isProductCodeExists(String productCode) {
        String sql = "SELECT COUNT(*) FROM products WHERE product_code = ? AND status = 'ACTIVE'";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, productCode);
            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return false;
    }

    /**
     * 从ResultSet创建Product对象（重用代码）
     */
    private Product createProductFromResultSet(ResultSet rs) throws SQLException {
        Product product = new Product();
        product.setId(rs.getInt("id"));
        product.setProductCode(rs.getString("product_code"));
        product.setProductName(rs.getString("product_name"));
        product.setCategoryId(rs.getInt("category_id"));
        product.setCategoryName(rs.getString("category_name"));
        product.setSupplierId(rs.getInt("supplier_id"));
        product.setSupplierName(rs.getString("supplier_name"));
        product.setUnit(rs.getString("unit"));
        product.setPurchasePrice(rs.getBigDecimal("purchase_price"));
        product.setSalePrice(rs.getBigDecimal("sale_price"));
        product.setShelfLifeDays(rs.getObject("shelf_life_days", Integer.class));
        product.setDescription(rs.getString("description"));
        product.setStatus(rs.getString("status"));
        product.setCreatedAt(rs.getTimestamp("created_at"));
        product.setUpdatedAt(rs.getTimestamp("updated_at"));
        product.setCurrentStock(rs.getBigDecimal("current_stock"));
        product.setMinStock(rs.getBigDecimal("min_stock"));
        product.setMaxStock(rs.getBigDecimal("max_stock"));
        product.setLastSaleDate(rs.getDate("last_sale_date"));
        product.setStockStatus(rs.getString("stock_status"));
        return product;
    }
}
